const models = require("../db/mysqldb/index");
const { Op } = require("sequelize");

class DataSystemUserRoleModel {
  /**
   * @description 批量删除
   * @param {*} role_id
   * @returns
   */
  static async deleteByRoleId({ role_id }) {
    return await models.sys_user_role.destroy({
      where: { role_id },
    });
  }
  /**
   * @description 批量删除
   * @param {*} user_id
   * @returns
   */
  static async deleteByUserId({ user_id }) {
    return await models.sys_user_role.destroy({
      where: { user_id },
    });
  }
  /**
   * @description 通过role_id和user_id删除
   * @param {*} role_id
   * @param {*} user_id
   * @returns
   */
  static async deleteByRoleIdAndUserId({ role_id, user_id }) {
    return await models.sys_user_role.destroy({
      where: { role_id, user_id },
    });
  }

  /**
   * @description 通过role_id和user_id删除
   * @param {*} role_id
   * @param {*} user_id
   * @returns
   */
  static async batchDestory({ role_id, user_ids }) {
    return await models.sys_user_role.destroy({
      where: {
        role_id,
        user_id: {
          [Op.in]: user_ids,
        },
      },
    });
  }

  /**
   * @description 通过role_id和user_id删除
   * @param {*} user_id
   * @param {*} role_ids
   * @returns
   */
  static async batchDestoryByUserId({ user_id, role_ids }) {
    return await models.sys_user_role.destroy({
      where: {
        user_id,
        role_id: {
          [Op.in]: role_ids,
        },
      },
    });
  }

  /**
   * @description 批量插入
   * @param {*} data
   * @returns
   */
  static async bulkCreate({data}) {
    return await models.sys_user_role.bulkCreate(data);
  }

  /**
   * @description 获取所有
   * @param {*} data
   * @returns
   */
  static async all() {
    return await models.sys_user_role.findAll();
  }

  /**
   * @description 当前用户的权限信息
   * @param {*} user_id
   * @returns
   */
  static async findRoleInfoByUserID(user_id) {
    let sql = `SELECT
    sr.role_id,
    sr.role_name,
    sr.role_key,
    sr.role_sort,
    sr.data_scope,
    sr.menu_check_strictly,
    sr.dept_check_strictly,
    sr.status,
    sr.remark
  FROM
    sys_user_role sur
    LEFT JOIN sys_role sr ON sur.role_id = sr.role_id 
  WHERE
    user_id = ${user_id}
    AND sr.del_flag = '0'`;
    return await models.sequelize.query(sql, {
      replacements: ["active"],
      type: models.sequelize.QueryTypes.SELECT,
    });
  }
  /**
   * @description 通过权限id获取用户信息
   * @param {*} user_id
   * @returns
   */
  static async findUserInfoByRoleId({ role_id, data_scope = "", size, limit,phonenumber,user_name }) {
    let querySql = '';
    if (phonenumber) {
      querySql += ` and phonenumber like '%${phonenumber}%'`
    }
    if (user_name) {
      querySql += ` and user_name like '%${user_name}%'`
    }
    let sql = `SELECT
    sr.user_id,
    sr.dept_id,
    sr.user_name,
    sr.user_number,
    sr.nick_name,
    sr.user_type,
    sr.email,
    sr.phonenumber,
    sr.sex,
    sr.avatar,
    sr.status,
    sr.remark,
    sr.create_time
  FROM
    sys_user_role sur
    LEFT JOIN sys_user sr ON sur.user_id = sr.user_id 
  WHERE
    role_id = ${role_id} ${querySql} ${data_scope}
    AND sr.del_flag = '0' limit ${(limit - 1) * size}, ${size};`;
    const result = await models.sequelize.query(sql, {
      replacements: ["active"],
      type: models.sequelize.QueryTypes.SELECT,
    });
    const count = await models.sequelize.query(
      `SELECT count(1) as count FROM
      sys_user_role sur
      LEFT JOIN sys_user sr ON sur.user_id = sr.user_id 
    WHERE
      role_id = ${role_id} ${querySql} ${data_scope}
      AND sr.del_flag = '0';`,
      { replacements: ["active"], type: models.sequelize.QueryTypes.SELECT }
    );
    return {
      result,
      count,
    };
  }
  /**
   * @description 通过权限id获取没有绑定的用户信息
   * @param {*} user_id
   * @returns
   */
  static async findUserInfoNotInRoleId({
    role_id,
    data_scope = "",
    limit,
    size,phonenumber,user_name
  }) {
    let querySql = '';
    if (phonenumber) {
      querySql += ` and phonenumber like '%${phonenumber}%'`
    }
    if (user_name) {
      querySql += ` and user_name like '%${user_name}%'`
    }
    let sql = `SELECT
      sr.user_id,
      sr.dept_id,
      sr.user_name,
      sr.user_number,
      sr.nick_name,
      sr.user_type,
      sr.email,
      sr.phonenumber,
      sr.sex,
      sr.avatar,
      sr.status,
      sr.remark,
      sr.create_time
    FROM
      sys_user sr WHERE sr.user_id not in (select user_id from sys_user_role WHERE role_id = ${role_id}) 
      AND sr.del_flag = '0' ${querySql} ${data_scope} limit ${(limit - 1) * size}, ${size}`;
    const result = await models.sequelize.query(sql, {
      replacements: ["active"],
      type: models.sequelize.QueryTypes.SELECT,
    });
    const count = await models.sequelize.query(
      `SELECT count(1) as count FROM
        sys_user sr WHERE sr.user_id not in (select user_id from sys_user_role WHERE role_id = ${role_id}) 
        AND sr.del_flag = '0' ${querySql} ${data_scope}`,
      { replacements: ["active"], type: models.sequelize.QueryTypes.SELECT }
    );
    return {
      result,
      count,
    };
  }
}
module.exports = {
  DataSystemUserRoleModel,
};
